package edu.zju.gis.dbfg.server.controller;

import edu.zju.gis.dbfg.common.Page;
import edu.zju.gis.dbfg.common.Result;
import edu.zju.gis.dbfg.server.model.ExternalDataArgs;
import edu.zju.gis.dbfg.server.model.ExternalDataSource;
import edu.zju.gis.dbfg.server.model.PgSource;
import edu.zju.gis.dbfg.server.model.vo.tableRecordsWithTotalsize;
import edu.zju.gis.dbfg.server.service.ExternalDataSourceService;
import edu.zju.gis.dbfg.server.service.PgSourceService;
import edu.zju.gis.dbfg.server.util.ExternalDataHelper;
import org.json.simple.JSONObject;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

@CrossOrigin
@Controller
@RequestMapping("/external")
public class ExternalDataController {
    @Autowired
    private PgSourceService pgSourceService;

    @Autowired
    private ExternalDataSourceService externalDataSourceService;
//    private List<JSONObject> pgSchemas;

    @RequestMapping(value = "/pg/all", method = RequestMethod.POST)
    @ResponseBody
    public Result getAll() {
        Result<List<PgSource>> result = new Result<>();
        return result.setBody(pgSourceService.search()).setCode(200);
    }

    @RequestMapping(value = "/connect", method = RequestMethod.POST)
    @ResponseBody
    public Result connect(@RequestBody ExternalDataArgs sourceConnect) {
        Result<String> result = new Result<>();
        try {
            ExternalDataHelper.getConnection(sourceConnect);
            ExternalDataHelper.close();
            result.setBody("连接外部数据库成功").setCode(200).setMessage("连接外部数据库成功");
        } catch (Exception e) {
            result.setBody("连接外部数据库失败").setCode(201).setMessage("连接外部数据库失败");
        }
        return result;
    }

    @RequestMapping(value = "/pg/tables", method = RequestMethod.POST)
    @ResponseBody
    public Result tablesGet(@RequestBody ExternalDataArgs sourceConnect) {
        Result<List<JSONObject>> result = new Result<>();
        switch (sourceConnect.getType()) {
            case "postgresql":
                Connection c = null;
                try {
                    Class.forName("org.postgresql.Driver");
                    String url = "jdbc:postgresql://" + sourceConnect.getIp() + ":" + sourceConnect.getPort() + "/" + sourceConnect.getDatabaseName();
                    c = DriverManager.getConnection(url,
                            sourceConnect.getUserName(), sourceConnect.getPassword());
//                    c = DriverManager.getConnection("jdbc:postgresql://101.132.135.1:5432/postgres", "postgres", "postgres");
                    Statement stmt = c.createStatement();
                    String selectSql = "select * from pg_tables where schemaname ='" + sourceConnect.getSchemaName() + "'";
//                    ResultSet rs = stmt.executeQuery("select * from pg_tables where schemaname = 'public'");
                    ResultSet rs = stmt.executeQuery(selectSql);
                    List<JSONObject> tables = new ArrayList<>();
                    while (rs.next()) {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("value", rs.getString("tablename"));
                        jsonObject.put("label", rs.getString("tablename"));
                        tables.add(jsonObject);
                        String name = rs.getString("tablename");
                        System.out.println("表的名字是" + name);
                    }
                    result.setBody(tables).setCode(200);
                    rs.close();
                    stmt.close();
                } catch (Exception e) {
                    e.printStackTrace();
                    System.err.println(e.getClass().getName() + ": " + e.getMessage());
                    result.setMessage("获取postgresql数据表失败").setCode(201);
                }
                break;
            default:
                result.setMessage("没有匹配到相应类型数据源").setCode(201);
                break;
        }
        return result;

    }


    @RequestMapping(value = "/pg/schemas", method = RequestMethod.POST)
    @ResponseBody
    public Result tableScheGet(@RequestBody ExternalDataArgs sourceConnect) {
        Result<List<JSONObject>> result = new Result<>();
        switch (sourceConnect.getType()) {
            case "postgresql":
                Connection c = null;
                try {
                    Class.forName("org.postgresql.Driver");
                    String url = "jdbc:postgresql://" + sourceConnect.getIp() + ":" + sourceConnect.getPort() + "/" + sourceConnect.getDatabaseName();
                    c = DriverManager.getConnection(url,
                            sourceConnect.getUserName(), sourceConnect.getPassword());
//                    c = DriverManager.getConnection("jdbc:postgresql://101.132.135.1:5432/postgres", "postgres", "postgres");
                    Statement stmt = c.createStatement();
                    String selectScheSql = "select * from information_schema.schemata";
//                    ResultSet rs = stmt.executeQuery("select * from pg_tables where schemaname = 'public'");
                    ResultSet rs = stmt.executeQuery(selectScheSql);
                    List<JSONObject> tables = new ArrayList<>();
                    while (rs.next()) {
                        JSONObject jsonObject = new JSONObject();
                        jsonObject.put("value", rs.getString("schema_name"));
                        jsonObject.put("label", rs.getString("schema_name"));
                        tables.add(jsonObject);
                        String name = rs.getString("schema_name");
                    }
                    result.setBody(tables).setCode(200);
                    rs.close();
                    stmt.close();
                } catch (Exception e) {
                    e.printStackTrace();
                    System.err.println(e.getClass().getName() + ": " + e.getMessage());
                    result.setMessage("获取postgresql数据库模式失败").setCode(201);
                }
                break;
            default:
                result.setMessage("没有匹配到相应类型数据源").setCode(201);
                break;
        }
        return result;
    }

    @RequestMapping(value = "/query", method = RequestMethod.POST)
    @ResponseBody
    public Result dataGet(@RequestBody ExternalDataArgs sourceConnect) {
        Result<List<JSONObject>> result = new Result<>();
        try {
            result.setBody(ExternalDataHelper.queryConstruct(sourceConnect)).setCode(200).setMessage("获取数据成功");
            ExternalDataHelper.close();
        } catch (Exception e) {
            result.setCode(201).setMessage("获取数据失败");
        }
        return result;
    }

    //获得用户个人所有的外部链接数据
    @RequestMapping(value = "/all", method = RequestMethod.GET)
    @ResponseBody
    public Result externalAll(@SessionAttribute("userId") String userId, Page page) {
        Page<ExternalDataSource> result = externalDataSourceService.search(userId, page);
        return Result.success().setBody(result);
    }

    @RequestMapping(value = "/add", method = RequestMethod.POST)
    @ResponseBody
    public Result externalInsert(@SessionAttribute("userId") String userId, @RequestBody ExternalDataArgs sourceConnect) {
        String uuid = UUID.randomUUID().toString();
        int result1 = 0;
        int result2 = 0;
        ExternalDataSource externalDataSource = new ExternalDataSource();
        externalDataSource.setId(uuid);
        externalDataSource.setType(sourceConnect.getType());
        externalDataSource.setDataName(sourceConnect.getDataName());
        externalDataSource.setDescription(sourceConnect.getDescription());
        externalDataSource.setUserid(userId);
        result1 = externalDataSourceService.insert(externalDataSource);
        switch (sourceConnect.getType()) {
            case "postgresql":
                PgSource pgSource = new PgSource();
                pgSource.setId(uuid);
                pgSource.setDatabase(sourceConnect.getDatabaseName());
                pgSource.setPassword(sourceConnect.getPassword());
                pgSource.setIP(sourceConnect.getIp());
                String connectionName = "jdbc:postgresql://" + sourceConnect.getIp() + ":" + sourceConnect.getPort() + "/" + sourceConnect.getDatabaseName();
                pgSource.setConnectionName(connectionName);
                pgSource.setPort(sourceConnect.getPort());
                pgSource.setUserName(sourceConnect.getUserName());
                pgSource.setDataName(sourceConnect.getDataName());
                pgSource.setTableName(sourceConnect.getTableName());
                pgSource.setSchemaName(sourceConnect.getSchemaName());
                pgSource.setFields(sourceConnect.getFields());
                pgSource.setTimeField(sourceConnect.getTimeField());
                pgSource.setSpatialField(sourceConnect.getSpatialField());
                result2 = pgSourceService.insert(pgSource);
            case "mysql":
            default:
        }
        if (result1 == 1 && result2 == 1) {
            return Result.success().setMessage("添加外部数据源成功");
        } else {
            return Result.error().setMessage("添加外部数据源失败");
        }
    }

    //获取某个表中的全部记录
    @RequestMapping(value = "/tablePreview/{id}", method = RequestMethod.POST)
    @ResponseBody
    public Result tablesGet(@PathVariable String id, int pageNo, int pageSize) {
        ExternalDataArgs sourceConnect = new ExternalDataArgs();
        ExternalDataSource externalDataSource = externalDataSourceService.searchById(id);
        PgSource pgSource = pgSourceService.searchById(id);
//        sourceConnect.setConnectionName(pgSource.getConnectionName());
        sourceConnect.setDatabaseName(pgSource.getDatabase());
        sourceConnect.setDataName(pgSource.getDataName());
        sourceConnect.setIp(pgSource.getIP());
        sourceConnect.setPort(pgSource.getPort());
        sourceConnect.setPassword(pgSource.getPassword());
        sourceConnect.setUserName(pgSource.getUserName());
        sourceConnect.setType(externalDataSource.getType());
        sourceConnect.setTableName(pgSource.getTableName());
        sourceConnect.setSchemaName(pgSource.getSchemaName());
        Result<tableRecordsWithTotalsize> result = new Result<>();
        try {
            result.setCode(200).setBody(ExternalDataHelper.pageSearh(sourceConnect, pageNo, pageSize));
            ExternalDataHelper.close();
        } catch (Exception e) {
            result.setMessage("数据预览失败").setCode(201);
        }
        return result;
    }

    @RequestMapping(value = "/pg/tableInfo/{id}", method = RequestMethod.POST)
    @ResponseBody
    public Result tableGet(@PathVariable String id) {
        PgSource pgSource = pgSourceService.searchById(id);
        return Result.success().setBody(pgSource);
    }

    @RequestMapping(value = "/pg/tableDelete/{id}", method = RequestMethod.POST)
    @ResponseBody
    public Result tablesDelete(@PathVariable String id) {
        Result<String> result = new Result<>();
        int res1 = externalDataSourceService.deleteByPrimaryKey(id);
        int res2 = pgSourceService.deleteByPrimaryKey(id);
        if (res1 == 1 && res2 == 1) {
            result.setMessage("删除成功").setCode(200);
        } else {
            result.setMessage("删除失败").setCode(201);
        }
        return result;
    }


}
